package CelestialDataAccess;

import javax.sql.rowset.*; //provides cached row set interface
import java.sql.*;         //provides connection object and sql exception

import java.util.UUID;
import java.util.ArrayList;
import java.util.List;

import CelestialDataStructures.*;

public class OrdersDataAccess extends DataAccessBase{

    public static final String ORDER_ID = "OrderID";
    public static final String CUST_ID = "CustomerID";
    public static final String EMP_ID = "EmployeeID";
    public static final String TRANS_ID = "TransactionID";
    public static final String ORDER_DATE = "OrderDate";
    public static final String DISPATCH_DATE = "DispatchID";
    public static final String STATUS = "Status";
    public static final String REMARKS = "Remarks";

    public static final String BILL_COUNTRY = "BillingCountry";
    public static final String BILL_CITY = "BillingCity";
    public static final String BILL_STATE = "BillingState";
    public static final String BILL_ADDR_L1 = "BillingAddressLine1";
    public static final String BILL_ADDR_L2 = "BillingAddressLine2";
    public static final String BILL_POSTAL = "BillingPostalCode";

    public static final String SHIP_COUNTRY = "ShippingCountry";
    public static final String SHIP_CITY = "ShippingCity";
    public static final String SHIP_STATE = "ShippingState";
    public static final String SHIP_ADDR_L1 = "ShippingAddressLine1";
    public static final String SHIP_ADDR_L2 = "ShippingAddressLine2";
    public static final String SHIP_POSTAL = "ShippingPostalCode";

    public static final String ORDER_ITEM_ID = "OrderItemID";
    public static final String PROD_ID = "ProductID";
    public static final String QTY = "Quantity";
    public static final String UNIT_PRICE = "UnitPrice";
    public static final String PRICE_OVERRIDE = "PriceOverride";

    /*************************************************************************/
    /* Get/Set methods */
    public SearchCriteria getSearchCriteria(){ return new OrdersSearchCriteria();}
    /*************************************************************************/
    /* Client methods */
    public int insertOrder(Order order) throws DataAccessException{

        /* insert order */
        CallableStatement stm = prepareStatement("{CALL uspInsertOrder(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}");
        try{
            stm.setString(ORDER_ID, order.getOrderID().toString());
            stm.setInt(CUST_ID, order.getCustomerID());
            stm.setInt(EMP_ID, order.getEmployeeID());
            stm.setString(TRANS_ID, order.getTransactionID().toString());
            stm.setDate(ORDER_DATE, java.sql.Date.valueOf(DateFormat.format(order.getOrderDate())));
            stm.setDate(DISPATCH_DATE, java.sql.Date.valueOf(DateFormat.format(order.getDispatchDate())));
            stm.setString(REMARKS, order.getRemarks());

            Address addrShipping = order.getShippingAddress();
            stm.setString(SHIP_COUNTRY, addrShipping.Country);
            stm.setString(SHIP_CITY,  addrShipping.City);
            stm.setString(SHIP_STATE,  addrShipping.State);
            stm.setString(SHIP_ADDR_L1, addrShipping.AddressLine1);
            stm.setString(SHIP_ADDR_L2,  addrShipping.AddressLine2);
            stm.setString(SHIP_POSTAL,  addrShipping.PostalCode);

            Address addrBilling = order.getBillingAddress();
            stm.setString(BILL_COUNTRY, addrBilling.Country);
            stm.setString(BILL_CITY,  addrBilling.City);
            stm.setString(BILL_STATE,  addrBilling.State);
            stm.setString(BILL_ADDR_L1, addrBilling.AddressLine1);
            stm.setString(BILL_ADDR_L2,  addrBilling.AddressLine2);
            stm.setString(BILL_POSTAL,  addrBilling.PostalCode);

            stm.execute();
            stm.close();
        }catch(SQLException ex){
            throw new DataAccessException(ex.getMessage());
        }
        /* insert order items */
        insertOrderItems(order);

        return 1;
    }
    /*-----------------------------------------------------------------------*/
    public int deleteOrder(UUID order_id) throws DataAccessException{

        CallableStatement stm = prepareStatement("{CALL uspDeleteOrder(?)}");
        try{
            stm.setString(ORDER_ID, order_id.toString());
            stm.execute();
            stm.close();
        }catch(SQLException ex){
            throw new DataAccessException(ex.getMessage());
        }
        return 1;
    }
    /*-----------------------------------------------------------------------*/
    public int updateOrder(Order order) throws DataAccessException{

        CallableStatement stm = prepareStatement("{CALL uspUpdateOrder(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}");
        try{
            stm.setString(ORDER_ID, order.getOrderID().toString());
            stm.setInt(CUST_ID, order.getCustomerID());
            stm.setInt(EMP_ID, order.getEmployeeID());
            stm.setString(TRANS_ID, order.getTransactionID().toString());
            stm.setDate(ORDER_DATE, java.sql.Date.valueOf(DateFormat.format(order.getOrderDate())));
            stm.setDate(DISPATCH_DATE, java.sql.Date.valueOf(DateFormat.format(order.getDispatchDate())));
            stm.setString(REMARKS, order.getRemarks());

            Address addrShipping = order.getShippingAddress();
            stm.setString(SHIP_COUNTRY, addrShipping.Country);
            stm.setString(SHIP_CITY,  addrShipping.City);
            stm.setString(SHIP_STATE,  addrShipping.State);
            stm.setString(SHIP_ADDR_L1, addrShipping.AddressLine1);
            stm.setString(SHIP_ADDR_L2,  addrShipping.AddressLine2);
            stm.setString(SHIP_POSTAL,  addrShipping.PostalCode);

            Address addrBilling = order.getBillingAddress();
            stm.setString(BILL_COUNTRY, addrBilling.Country);
            stm.setString(BILL_CITY,  addrBilling.City);
            stm.setString(BILL_STATE,  addrBilling.State);
            stm.setString(BILL_ADDR_L1, addrBilling.AddressLine1);
            stm.setString(BILL_ADDR_L2,  addrBilling.AddressLine2);
            stm.setString(BILL_POSTAL,  addrBilling.PostalCode);

            stm.execute();
            stm.close();
        }catch(SQLException ex){
            throw new DataAccessException(ex.getMessage());
        }
        /* delete all order items */
        clearOrderItems(order);

        /* re-insert order items */
        insertOrderItems(order);

        return 1;
    }
    /*-----------------------------------------------------------------------*/
    public Order getOrder(UUID order_id) throws DataAccessException{

        CallableStatement stm = prepareStatement("{CALL uspGetOrder(?)}");
        CachedRowSet crs;

        DataAccessBase.open();
        try{
            stm.setString(ORDER_ID, order_id.toString());
            crs = getData(stm);
            stm.close();

            crs.last(); //assert 1 row only

            if(crs.getRow()==0)
                return null;
            else
                return Order.createOrder(crs);
        }catch(SQLException ex){
            throw new DataAccessException(ex.getMessage());
        }
    }
    /*-----------------------------------------------------------------------*/
    public void getOrdersByCustomerID(int cust_id, List<Order> lst) throws DataAccessException{
        
        CallableStatement stm = prepareStatement("{CALL uspGetOrdersByCustomerID(?)}");
        CachedRowSet crs;
        try{
            stm.setInt(CUST_ID, cust_id);
            crs = getData(stm);
            stm.close();

            crs.absolute(-1);
            while(crs.next())
                lst.add(getOrder(UUID.fromString(crs.getString(ORDER_ID))));
        }catch (SQLException ex){
            throw new DataAccessException(ex.getMessage());
        }
    }
    /*************************************************************************/
    /* Helpers */
    private void insertOrderItems(Order order) throws DataAccessException{

        CallableStatement stm;
        for(OrderItem item : order.getOrderItems()){

            stm = prepareStatement("{CALL uspInsertOrderItem(?,?,?,?,?,?,?)}");
            try{
                stm.setString(ORDER_ID, order.getOrderID().toString());
                stm.setString(PROD_ID, item.m_strProductID);
                stm.setInt(QTY, item.m_iQuantity);
                stm.setDouble(UNIT_PRICE, item.m_dblUnitPrice);
                stm.setBoolean(PRICE_OVERRIDE, item.m_bPriceOverride);
                stm.setString(REMARKS, item.m_strRemarks);

                stm.execute();
                stm.close();
            }catch(SQLException ex){
                throw new DataAccessException(ex.getMessage());
            }
        }
    }
    /*-----------------------------------------------------------------------*/
    private void clearOrderItems(Order order) throws DataAccessException{

        CallableStatement stm = prepareStatement("{CALL uspClearOrderItems(?)}");
        try{
            stm.setString(ORDER_ID, order.getOrderID().toString());
            stm.execute();
            stm.close();
        }catch(SQLException ex){
            throw new DataAccessException(ex.getMessage());
        }
    }
    /*************************************************************************/
    /* Test */
    public static void main(String[] args){

      OrdersDataAccess daOrders = new OrdersDataAccess();
      Order order;
      List<Order> lst = new ArrayList<Order>();
      try{
          order = daOrders.getOrder(UUID.fromString("7C896734-8F98-40AA-B5A7-351E529904CD"));
          if(order!=null)
              System.out.println(order.getOrderID() + "\t" + order.getTotalAmount());

          daOrders.getOrdersByCustomerID(100000, lst);

          if(lst.isEmpty()) return;

          for(Order o : lst){
              System.out.println(o.getRemarks());
              for(OrderItem item : o.getOrderItems())
                  System.out.println("\t" + item.m_strProductID + "\t" + item.m_iQuantity + "\t" + item.m_dblUnitPrice + "\t" + item.getExtendedPrice());
          }
      } catch(DataAccessException ex){
          System.out.println(ex.getMessage());
      }
    }
    /*************************************************************************/
    private class OrdersSearchCriteria extends SearchCriteria{

        /*********************************************************************/
        /* Constructors */
        OrdersSearchCriteria(){

            m_strSelectClause = "SELECT * FROM tblOrders, tblOrderItems";

            m_mapValues.put(ORDER_ID, null);
            m_mapValues.put(CUST_ID, null);

            m_mapOperators.put(ORDER_ID, SearchCriteria.EQU);
            m_mapOperators.put(CUST_ID, SearchCriteria.EQU);

        }
        /*********************************************************************/
    }
    /*************************************************************************/

}
